<?php
// 显示所有错误
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// 加载数据库配置
require_once __DIR__ . '/../config/database.php';

$results = [];
$success = true;

try {
    // 创建PDO连接
    $dsn = "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET;
    $pdo = new PDO($dsn, DB_USERNAME, DB_PASSWORD);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $results[] = "成功连接到数据库 " . DB_NAME;
    
    // 获取当前表列表
    $tables = [];
    $stmt = $pdo->query("SHOW TABLES LIKE '".DB_PREFIX."%'");
    while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
        $tables[] = $row[0];
    }
    
    if (count($tables) > 0) {
        // 检查是否有外键约束
        $existingConstraints = [];
        foreach ($tables as $table) {
            $query = "
                SELECT CONSTRAINT_NAME
                FROM information_schema.TABLE_CONSTRAINTS
                WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
                AND TABLE_SCHEMA = '".DB_NAME."'
                AND TABLE_NAME = '{$table}'
            ";
            $stmt = $pdo->query($query);
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $existingConstraints[] = $row['CONSTRAINT_NAME'];
            }
        }
        
        if (count($existingConstraints) > 0) {
            $results[] = "发现 " . count($existingConstraints) . " 个外键约束: " . implode(", ", $existingConstraints);
            
            // 设置外键检查为0
            $pdo->exec("SET FOREIGN_KEY_CHECKS = 0");
            $results[] = "已禁用外键检查";
            
            // 修改setup_tables.php文件，使其在创建表时添加IF NOT EXISTS条件
            $setupFile = __DIR__ . '/setup_tables.php';
            if (file_exists($setupFile)) {
                $content = file_get_contents($setupFile);
                $newContent = $content;
                
                // 检查是否已经添加了"添加外键约束"部分，如果没有，添加它
                if (strpos($content, 'ADD CONSTRAINT IF NOT EXISTS') === false) {
                    // 找到文件结尾的位置（在最后一个?>前）
                    $endPos = strrpos($content, '?>');
                    
                    // 添加外键约束代码
                    $constraintsCode = <<<'EOD'
    
    // 添加外键约束
    try {
        // 设置外键检查为0，允许添加外键
        $pdo->exec("SET FOREIGN_KEY_CHECKS = 0");
        
        // 为成就表添加游戏外键约束
        $pdo->exec("ALTER TABLE `".DB_PREFIX."achievements` 
                    ADD CONSTRAINT IF NOT EXISTS `".DB_PREFIX."achievements_game_id_fk` 
                    FOREIGN KEY (`game_id`) REFERENCES `".DB_PREFIX."games` (`id`) 
                    ON DELETE SET NULL ON UPDATE CASCADE");
                    
        // 为游戏记录表添加用户外键约束
        $pdo->exec("ALTER TABLE `".DB_PREFIX."game_records` 
                    ADD CONSTRAINT IF NOT EXISTS `".DB_PREFIX."game_records_user_id_fk` 
                    FOREIGN KEY (`user_id`) REFERENCES `".DB_PREFIX."users` (`id`) 
                    ON DELETE CASCADE ON UPDATE CASCADE");
                    
        // 为游戏记录表添加游戏外键约束
        $pdo->exec("ALTER TABLE `".DB_PREFIX."game_records` 
                    ADD CONSTRAINT IF NOT EXISTS `".DB_PREFIX."game_records_game_id_fk` 
                    FOREIGN KEY (`game_id`) REFERENCES `".DB_PREFIX."games` (`id`) 
                    ON DELETE CASCADE ON UPDATE CASCADE");
                    
        // 为用户成就表添加用户外键约束
        $pdo->exec("ALTER TABLE `".DB_PREFIX."user_achievements` 
                    ADD CONSTRAINT IF NOT EXISTS `".DB_PREFIX."user_achievements_user_id_fk` 
                    FOREIGN KEY (`user_id`) REFERENCES `".DB_PREFIX."users` (`id`) 
                    ON DELETE CASCADE ON UPDATE CASCADE");
                    
        // 为用户成就表添加成就外键约束
        $pdo->exec("ALTER TABLE `".DB_PREFIX."user_achievements` 
                    ADD CONSTRAINT IF NOT EXISTS `".DB_PREFIX."user_achievements_achievement_id_fk` 
                    FOREIGN KEY (`achievement_id`) REFERENCES `".DB_PREFIX."achievements` (`id`) 
                    ON DELETE CASCADE ON UPDATE CASCADE");
                    
        // 为用户成就表添加游戏外键约束
        $pdo->exec("ALTER TABLE `".DB_PREFIX."user_achievements` 
                    ADD CONSTRAINT IF NOT EXISTS `".DB_PREFIX."user_achievements_game_id_fk` 
                    FOREIGN KEY (`game_id`) REFERENCES `".DB_PREFIX."games` (`id`) 
                    ON DELETE SET NULL ON UPDATE CASCADE");
        
        // 重新启用外键检查
        $pdo->exec("SET FOREIGN_KEY_CHECKS = 1");
        
        $message .= "<div class='alert alert-success'>外键约束添加成功！</div>";
    } catch (PDOException $e) {
        // 如果外键已经存在，不影响整体流程
        $message .= "<div class='alert alert-info'>部分外键约束可能已存在: " . $e->getMessage() . "</div>";
    }

EOD;
                    
                    // 将约束代码插入到文件结尾前
                    if ($endPos !== false) {
                        $newContent = substr($content, 0, $endPos) . $constraintsCode . "\n" . substr($content, $endPos);
                    } else {
                        $newContent = $content . "\n" . $constraintsCode;
                    }
                    
                    file_put_contents($setupFile, $newContent);
                    $results[] = "已更新 setup_tables.php 文件，添加了外键约束处理代码";
                } else {
                    $results[] = "setup_tables.php 文件已包含外键约束处理代码，无需修改";
                }
            } else {
                $success = false;
                $results[] = "无法找到 setup_tables.php 文件";
            }
            
            // 对于有外键问题的表，建议使用cleanup_database.php完全清理
            $results[] = "<strong>建议操作：</strong> 由于已存在外键约束，请先运行 cleanup_database.php 清理所有表，然后再重新运行 setup_tables.php";
        } else {
            $results[] = "没有发现外键约束，可能是其他问题导致";
        }
    } else {
        $results[] = "数据库中未发现任何表，可以直接运行 setup_tables.php";
    }
    
} catch (PDOException $e) {
    $success = false;
    $results[] = "<strong>错误：</strong> " . $e->getMessage();
}
?>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>KidsMind - 修复外键约束</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css">
</head>
<body>
    <div class="container mt-5">
        <div class="row justify-content-center">
            <div class="col-md-8">
                <div class="card shadow">
                    <div class="card-header bg-warning text-white">
                        <h4 class="mb-0">KidsMind 修复外键约束工具</h4>
                    </div>
                    <div class="card-body">
                        <h5 class="card-title mb-4">诊断结果</h5>
                        
                        <?php foreach ($results as $result): ?>
                            <div class="alert alert-info"><?php echo $result; ?></div>
                        <?php endforeach; ?>
                        
                        <div class="alert <?php echo $success ? 'alert-success' : 'alert-danger'; ?>">
                            <?php if ($success): ?>
                                <strong>诊断完成！</strong> 请按照建议操作处理外键约束问题。
                            <?php else: ?>
                                <strong>诊断失败！</strong> 请检查错误信息并手动解决问题。
                            <?php endif; ?>
                        </div>
                        
                        <div class="mt-4">
                            <p><strong>推荐操作顺序：</strong></p>
                            <ol>
                                <li>运行 <a href="cleanup_database.php" class="fw-bold">cleanup_database.php</a> 清理所有现有表</li>
                                <li>然后运行 <a href="setup_tables.php">setup_tables.php</a> 重新创建所有表</li>
                                <li>最后运行 <a href="add_test_data.php">add_test_data.php</a> 添加测试数据</li>
                            </ol>
                        </div>
                        
                        <div class="mt-4">
                            <a href="cleanup_database.php" class="btn btn-danger">清理数据库</a>
                            <a href="setup_tables.php" class="btn btn-primary">创建数据表</a>
                            <a href="add_test_data.php" class="btn btn-success">添加测试数据</a>
                            <a href="test_login.php" class="btn btn-secondary">登录诊断</a>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</body>
</html> 